﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// 通用/公共数据访问类
    /// </summary>
    public class DBHelper
    {
        //将UI层的App.Config引入，完成数据库连接
        private static string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public static SqlConnection conn = new SqlConnection(str);

        DBHelper()
        {
            //conn = new SqlConnection();
           
        }

        /// 一般执行查询数据集操作
        public static DataSet GetDataSet(string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//定义用户填充数据集DataSet的SQL数据适配器
            DataSet ds = new DataSet();//定义数据集容器
            sda.Fill(ds);//使用数据适配器的返回结果填充数据集容器
            return ds;
        }

        // 通过SQL命令执行结果进行DataSet返回
        public static DataSet QueryData(SqlCommand cmd)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
                return null;

            
            conn = new SqlConnection(str);//创建DB连接
            //判断数据库的连接状态是否是打开
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            cmd.Connection = conn;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// 读库操作，提供对查询结果的影响
        public static SqlDataReader ExecueReader(SqlCommand cmd)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;//连接数据库
            SqlDataReader reader;
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        // 执行SQL查询，返回一项数据结果（值），返回第一行第一列
        public static object GetScalar(SqlCommand cmd)
        {
            cmd.Connection = conn;//连接数据库
            object obj = new SqlCommand();//建立objec对象，注意其实例化数据类型
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                obj = cmd.ExecuteScalar();//调用SqlCommand对象的ExecuteScalar方法执行SQL
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();//关闭DB连接
            }
            return obj;
        }
        /// 实现数据库的CRUD，主要返回的数据操作影响行数的统计数值
        public static int ExecuteSQL(SqlCommand cmd)
        {
            conn = new SqlConnection(str);//连接DB
            cmd.Connection = conn;
            if (conn.State == ConnectionState.Closed)
                conn.Open();//打开DB
            int num = 0;
            try
            {
                num = cmd.ExecuteNonQuery();//调用SqlCommand的ExecuteNonQuery方法执行SQL
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();//关闭DB连接
            }
            return num;
        }
        /// 读数据库的相片信息
        public static MemoryStream GetPhoto(SqlCommand cmd)
        {
            cmd.Connection = conn;
            try
            {
                //如果DB连接为关闭状态，进行打开操作
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                MemoryStream blobData = new MemoryStream();//创建数据流对象
                Byte[] image = new Byte[0];//创建二进制对象

                var obj = cmd.ExecuteScalar();//读库

                // 如果读库结果为空，返回null；如果非空进行转型后返回
                if (Convert.IsDBNull(obj))
                    return null;
                else
                {
                    image = (Byte[])obj;//将读库结果进行二进制序列
                    blobData = new MemoryStream(image);//将二进制序列结果转成数据流
                    return blobData;
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
